﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DBUtil.MetaData;
using System.Linq;
using DBUtil.Generators;
using System.Collections.Concurrent;
using System.Threading;
using MySqlConnector;
using DBUtil.Provider.MySql.MetaData;
using DBUtil.Expressions;
using DBUtil.Provider.MySql.Expressions;

namespace DBUtil.Provider.MySql
{
    /// <summary>
    /// MySql操作对象
    /// </summary>
    public partial class MySqlAccess : DBAccess
    {
        #region 创建IDbCommand
        /// <summary>
        /// 创建IDbCommand
        /// </summary>
        /// <returns></returns>
        protected override IDbCommand CreateCommand()
        {
            return new MySqlCommand();
        }
        #endregion

        #region 创建IDataAdapter(根据命令对象)
        /// <summary>
        /// 创建IDataAdapter
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns></returns>
        protected override IDataAdapter CreateAdapter(IDbCommand cmd)
        {
            return new MySqlDataAdapter(cmd as MySqlCommand);
        }
        #endregion

        #region CreatePara 创建参数
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public override IDataParameter CreatePara()
        {
            return new MySqlParameter();
        }
        #endregion

        #region 初始化
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="DBConn">连接字符串</param>
        /// <param name="Settings">设置</param>
        public MySqlAccess(string DBConn, DBSettings Settings)
        {
            this.Settings = Settings;
            this.DBConn = DBConn;
            this.Conn = new MySqlConnection(DBConn);
            InitServerInfo();
        }

        private void InitServerInfo()
        {
            this.ParaPrefix = "@";
            this.DBType = Consts.ServerType;
        }
        #endregion        

        #region OpenTestAndCheckDataBaseInfo
        /// <summary>
        /// 进行连接测试并检测数据库版本
        /// </summary>
        /// <returns></returns>
        public override (bool success, string version, string databaseName) OpenTestAndCheckDataBaseInfo()
        {
            try
            {
                var versionstr = SelectScalar<string>("select @@VERSION");
                var dbname = SelectScalar<string>("select database()");
                return (true, versionstr, dbname);
            }
            catch (Exception ex)
            {
                return (false, ex?.Message, ex?.Message);
            }
        }
        #endregion

        #region GetSqlForPageSize
        /// <summary>获得分页的查询语句
        /// </summary>
        /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序字句如:order by id</param>
        /// <param name="PageSize">页面大小,如:10</param>
        /// <param name="PageIndex">页面索引从1开始,如:1</param>
        /// <returns>返回示例:select id,name from person where age>10 order by id limit 0,10 </returns>
        public override string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex)
        {
            EnsureCheckDataBaseInfo();
            string sql = string.Format("{0} {1} limit {2},{3}", selectSql, strOrder, (PageIndex - 1) * PageSize, PageSize);
            return sql;
        }
        #endregion

        #region Judge系列: 判断表/视图/列/存储过程是否存在
        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public override bool JudgeProcedureExist(string procName)
        {
            string sql = $"show PROCEDURE status where Db ='{DataBase}' and name='{procName}' and `type` = 'PROCEDURE'";
            var dt = SelectDataTable(sql);
            return dt.Rows.Count > 0;
        }
        #endregion

        #region CreateNewDB
        /// <summary>
        /// 根据当前的数据库类型和连接字符串创建一个新的数据库操作对象
        /// </summary>
        /// <param name="act">构建新的数据库操作对象时允许重新配置行为规则</param>
        /// <returns></returns>
        public override DBAccess CreateNewDB(Action<DBSettings> act = null)
        {
            var settings = Settings.Clone() as DBSettings;
            act?.Invoke(settings);
            return new MySqlAccess(DBConn, settings);
        }
        #endregion

        #region 基于数据库的Id和流水号生成器
        private static ConcurrentDictionary<string, bool> _initGenerators = new ConcurrentDictionary<string, bool>();
        private void EnsureInitGenerator()
        {
            if (!_initGenerators.TryGetValue(DBConn, out bool flag))
            {
                lock (typeof(DBAccess))
                {
                    if (!_initGenerators.TryGetValue(DBConn, out bool flag2))
                    {
                        var b = InitGenerator();
                        if (b)
                        {
                            _initGenerators.TryAdd(DBConn, true);
                        }
                    }
                }
            }
        }
        private bool InitGenerator()
        {
            if (!JudgeTableExist("__generator_id"))
            {
                #region 新建表 __generator_id
                ExecuteSql(@"
create table __generator_id(
	 tablename varchar(50),
	 colname varchar(50),
	 currentid bigint,
	 primary key(tablename,colname)
)");
                #endregion
            }
            if (!JudgeProcedureExist("__proc_generateid"))
            {
                #region 新建存储过程 __proc_generateid
                ExecuteSql(@"
create PROCEDURE __proc_generateid(tablename varchar(50),colname varchar(50))
BEGIN
declare current bigint;
declare strsql varchar(1000);
-- 先从id表里查找
select currentid into current from __generator_id where tablename = tablename and colname = colname limit 0,1;
if current is null then
	set strsql=concat('select max(',colname,') into @vv from ',tablename);
	set @v_sql=strsql; 
    prepare stmt from @v_sql;
    EXECUTE stmt; 
	deallocate prepare stmt;
	set current = @vv;
	if current is null then
			BEGIN
				insert into __generator_id(tablename, colname, currentid) values(tablename, colname, 1);
				select 1;
			END;
    else
		set current=current+1;
		insert into __generator_id(tablename, colname, currentid) values(tablename, colname, current);
		select current;  
    end IF;
else
	set current = current+1;
	update __generator_id set currentid = current where tablename = tablename and colname = colname;
	select current;
END IF;
END
");
                #endregion
            }
            if (!JudgeTableExist("__generator_sno"))
            {
                #region 新建表(流水号生成) __generator_sno
                ExecuteSql(@"
create table __generator_sno(
	tablename varchar(50),
	colname varchar(50),
	nowstr datetime,
	currentno bigint,
	primary key(tablename,colname,nowstr)
)");
                #endregion
            }
            if (!JudgeProcedureExist("__proc_generatesno"))
            {
                #region 新建存储过程(流水号生成) __proc_generatesno
                ExecuteSql(@"
create PROCEDURE __proc_generatesno(
tablename varchar(50),
colname varchar(50),
nowstr varchar(200),
likestr varchar(200),
startindex int -- 注意:程序传入的startindex是以0起始,而数据库中是以1起始
)
BEGIN
declare current bigint;
declare strsql varchar(1000);
declare currentsno varchar(1000);

-- 清空超过30分钟的流水号生成控制
delete from __generator_sno where tablename = tablename and colname = colname and nowstr<date_add(STR_TO_DATE(nowstr, '%Y-%m-%d %H:%i:%s'), interval -30 minute);
-- 先从流水号表里查找
select currentno into current from __generator_sno where tablename = 'test' and colname = 'name' and nowstr=STR_TO_DATE(nowstr, '%Y-%m-%d %H:%i:%s') limit 0,1;
if current is null then
  -- 表里尚未缓存,从实际表里查找流水号列的最大值
  begin
    set strsql=concat('select ',colname,' into @vv from ',tablename,' where ',colname,' like ''',likestr,''' order by ',colname,' desc');
		set @v_sql=strsql; 
    prepare stmt from @v_sql;
    EXECUTE stmt; 
		deallocate prepare stmt;
		set currentsno = @vv;
	if(currentsno is null) then
		begin
			insert into __generator_sno(tablename, colname,nowstr, currentno) values(tablename, colname,STR_TO_DATE(nowstr, '%Y-%m-%d %H:%i:%s'), 1);
			select 1;
		end;
  else	
			set current=CAST(SUBSTRING(currentsno,startindex+1,100) as SIGNED);
			set current=current+1;
			insert into __generator_sno(tablename, colname,nowstr, currentno) values(tablename, colname,STR_TO_DATE(nowstr, '%Y-%m-%d %H:%i:%s'), current);
			select current;
  end IF;
	END;
else
    begin
        set current = current+1;
				update __generator_sno set currentno = current where tablename = tablename and colname = colname and nowstr=STR_TO_DATE(nowstr, '%Y-%m-%d %H:%i:%s');
				select current;
   end;
END IF;
END;
");
                #endregion
            }
            return true;
        }

        public override long NewId(string tableName, string colName)
        {
            var id = SelectScalar<string>($"call __proc_generateid('{tableName}','{colName}')");
            return long.Parse(id);
        }

        public override void ResetId(string tableName, string colName)
        {
            Delete("__generator_id", $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
        }

        public override string NewSNO(string tableName, string colName, SerialFormat format)
        {
            //long no = long.Parse(SelectScalar<string>($"call __proc_generatesno ('{tableName}','{colName}','{snoNow.ToString("yyyy-MM-dd HH:mm:ss")}', '{likestr}',{startindex})"));
            //return no;
            return "1";
        }

        public override void ResetSNO(string tableName, string colName)
        {
            Delete("__generator_sno", $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
        }

        public override List<long> NewIds(string tableName, string colName, int count)
        {
            throw new NotImplementedException();
        }

        public override List<string> NewSNOs(string tableName, string colName, SerialFormat format, int count)
        {
            throw new NotImplementedException();
        }

        public override bool JudgeTriggerExist(string triggerName)
        {
            throw new NotImplementedException();
        }
        #endregion

        #region 数据库管理对象 Manage
        private DBManage _dbmanage = null;
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public override DBManage Manage => _dbmanage != null ? _dbmanage : _dbmanage = new MySqlManage(this);
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        public override DateTimeExpression Expression_DateTime => MySqlDateTimeExpression.Default;
        #endregion
    }
}
